package com.jlu.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.Date;

public class ExcelWriteTest {

  String path = "D:\\BaiduNetdiskDownload\\POI&EasyExcel\\POI\\src\\main\\resources\\";

//  03版excel
  @Test
  public void write03() throws Exception {
//    创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
//    创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
//    创建第一行
    Row row1 = sheet.createRow(0);
//    创建第一列
    Cell cell1 = row1.createCell(0);
//    创建第二列
    Cell cell2 = row1.createCell(0);
//    给第一行第一列赋值
    cell1.setCellValue("第一个execldemo");
//    给第一行第二列赋值
    cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//    生成xls
    FileOutputStream outputStream = new FileOutputStream(path + "Demo.xls");
    workbook.write(outputStream);
    outputStream.close();
    System.out.println("生成成功");
  }

  //  07版excel
  @Test
  public void write07() throws Exception {
//    创建一个工作簿
    Workbook workbook = new XSSFWorkbook();
//    创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
//    创建第一行
    Row row1 = sheet.createRow(0);
//    创建第一列
    Cell cell1 = row1.createCell(0);
//    创建第二列
    Cell cell2 = row1.createCell(0);
//    给第一行第一列赋值
    cell1.setCellValue("第一个execldemo");
//    给第一行第二列赋值
    cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//    生成xlsx
    FileOutputStream outputStream = new FileOutputStream(path + "Demo.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    System.out.println("生成成功");
  }


//  03版大数据写入
  @Test
  public void write03BigData() throws Exception {

//    开始时间
    long begin = System.currentTimeMillis();
//    创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
//    创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
//    写入数据
    for (int i = 0; i < 65536; i++) {
      Row row = sheet.createRow(i);
      for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(j);
      }
    }
//    生成xls
    FileOutputStream outputStream = new FileOutputStream(path + "BigData03.xls");
    workbook.write(outputStream);
    outputStream.close();
//    结束时间
    long end = System.currentTimeMillis();
    System.out.println("用时:" + (double)(end - begin)/1000 + "s");
  }

  //  07版大数据写入
  @Test
  public void write07BigData() throws Exception {

//    开始时间
    long begin = System.currentTimeMillis();
//    创建一个工作簿
    Workbook workbook = new XSSFWorkbook();
//    创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
//    写入数据
    for (int i = 0; i < 65536; i++) {
      Row row = sheet.createRow(i);
      for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(j);
      }
    }
//    生成xlsx
    FileOutputStream outputStream = new FileOutputStream(path + "BigData07.xlsx");
    workbook.write(outputStream);
    outputStream.close();
//    结束时间
    long end = System.currentTimeMillis();
    System.out.println("用时:" + (double)(end - begin)/1000 + "s");
  }

  //  07升级版大数据写入
  @Test
  public void write07BigDataS() throws Exception {

//    开始时间
    long begin = System.currentTimeMillis();
//    创建一个工作簿
    Workbook workbook = new SXSSFWorkbook();
//    创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
//    写入数据
    for (int i = 0; i < 65536; i++) {
      Row row = sheet.createRow(i);
      for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(j);
      }
    }
//    生成xlsx
    FileOutputStream outputStream = new FileOutputStream(path + "BigData07S.xlsx");
    workbook.write(outputStream);
    outputStream.close();
//    切记要除临时文件
    ((SXSSFWorkbook)workbook).dispose();
//    结束时间
    long end = System.currentTimeMillis();
    System.out.println("用时:" + (double)(end - begin)/1000 + "s");
  }
}
